# src/crud.py
"""
CRUD layer using raw SQL and psycopg2.
All DB access goes through functions in this module.

Guidelines implemented:
- Prepared statements (%s) to avoid SQL injection
- Transaction management for multi-step operations
- Proper error handling (raises exceptions upward for FastAPI to convert)
- Uses RealDictCursor for easy JSON-serializable dict rows
"""

from typing import Any, Dict, List, Optional, Tuple
from datetime import date, datetime
import logging

import psycopg2
from psycopg2.extras import RealDictCursor

from .database.connection import get_conn


logger = logging.getLogger(__name__)


# Helper utilities
def _fetchone(query: str, params: Tuple = ()) -> Optional[Dict[str, Any]]:
    with get_conn() as conn:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute(query, params)
            return cur.fetchone()


def _fetchall(query: str, params: Tuple = ()) -> List[Dict[str, Any]]:
    with get_conn() as conn:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute(query, params)
            return cur.fetchall()


def _execute(query: str, params: Tuple = ()) -> int:
    with get_conn() as conn:
        with conn.cursor() as cur:
            cur.execute(query, params)
            return cur.rowcount


# Patients CRUD
def create_patient(payload: Dict[str, Any]) -> Dict[str, Any]:
    """
    Inserts a new patient and returns created row.
    Expects keys: first_name, last_name, date_of_birth (YYYY-MM-DD), optional: gender,email,phone,address,emergency_contact
    """
    q = """
    INSERT INTO hms.patients
      (first_name, last_name, date_of_birth, gender, email, phone, address, emergency_contact)
    VALUES (%s,%s,%s,%s,%s,%s,%s,%s)
    RETURNING *;
    """
    params = (
        payload.get("first_name"),
        payload.get("last_name"),
        payload.get("date_of_birth"),
        payload.get("gender"),
        payload.get("email"),
        payload.get("phone"),
        payload.get("address"),
        payload.get("emergency_contact"),
    )
    return _fetchone(q, params)


def get_patient(patient_id: int) -> Optional[Dict[str, Any]]:
    return _fetchone("SELECT * FROM hms.patients WHERE patient_id = %s", (patient_id,))


def list_patients(limit: int = 20, offset: int = 0, name_filter: Optional[str] = None) -> List[Dict[str, Any]]:
    if name_filter:
        q = """
        SELECT * FROM hms.patients
        WHERE lower(first_name || ' ' || last_name) LIKE %s
        ORDER BY patient_id
        LIMIT %s OFFSET %s;
        """
        return _fetchall(q, (f"%{name_filter.lower()}%", limit, offset))
    q = "SELECT * FROM hms.patients ORDER BY patient_id LIMIT %s OFFSET %s;"
    return _fetchall(q, (limit, offset))


def update_patient(patient_id: int, updates: Dict[str, Any]) -> Optional[Dict[str, Any]]:
    allowed = ["first_name", "last_name", "date_of_birth", "gender", "email", "phone", "address", "emergency_contact", "is_active"]
    sets = []
    params = []
    for k in allowed:
        if k in updates:
            sets.append(f"{k} = %s")
            params.append(updates[k])
    if not sets:
        return get_patient(patient_id)
    params.append(patient_id)
    q = f"UPDATE hms.patients SET {', '.join(sets)}, updated_at = COALESCE(updated_at, now()) WHERE patient_id = %s RETURNING *;"
    return _fetchone(q, tuple(params))


def delete_patient(patient_id: int) -> int:
    return _execute("DELETE FROM hms.patients WHERE patient_id = %s;", (patient_id,))


# Medical staff 
def get_staff(staff_id: int) -> Optional[Dict[str, Any]]:
    return _fetchone("SELECT * FROM hms.medical_staff WHERE staff_id = %s;", (staff_id,))


def list_staff(limit: int = 50, offset: int = 0) -> List[Dict[str, Any]]:
    return _fetchall("SELECT * FROM hms.medical_staff ORDER BY staff_id LIMIT %s OFFSET %s;", (limit, offset))


# Appointments CRUD
def create_appointment(payload: Dict[str, Any]) -> Dict[str, Any]:
    """
    Creates appointment inside a transaction. Overlap prevention handled by DB trigger.
    Expected keys: patient_id, staff_id, department_id (optional), appointment_datetime (ISO string), duration_minutes, reason, status
    """
    q = """
    INSERT INTO hms.appointments
      (patient_id, staff_id, department_id, appointment_datetime, duration_minutes, reason, status)
    VALUES (%s,%s,%s,%s,%s,%s,%s)
    RETURNING *;
    """
    params = (
        payload["patient_id"],
        payload["staff_id"],
        payload.get("department_id"),
        payload["appointment_datetime"],
        payload["duration_minutes"],
        payload.get("reason"),
        payload.get("status", "scheduled"),
    )
    return _fetchone(q, params)


def get_appointment(appointment_id: int) -> Optional[Dict[str, Any]]:
    return _fetchone("SELECT * FROM hms.appointments WHERE appointment_id = %s;", (appointment_id,))


def list_appointments(limit: int = 50, offset: int = 0, date_from: Optional[str] = None, staff_id: Optional[int] = None, patient_id: Optional[int] = None) -> List[Dict[str, Any]]:
    q = """
    SELECT a.*, p.first_name AS patient_first_name, p.last_name AS patient_last_name,
           s.first_name AS staff_first_name, s.last_name AS staff_last_name,
           d.name AS department_name
    FROM hms.appointments a
    JOIN hms.patients p ON p.patient_id = a.patient_id
    LEFT JOIN hms.medical_staff s ON s.staff_id = a.staff_id
    LEFT JOIN hms.departments d ON d.department_id = a.department_id
    """
    where = []
    params: List[Any] = []
    if date_from:
        where.append("a.appointment_datetime::date >= %s")
        params.append(date_from)
    if staff_id:
        where.append("a.staff_id = %s")
        params.append(staff_id)
    if patient_id:
        where.append("a.patient_id = %s")
        params.append(patient_id)
    if where:
        q += " WHERE " + " AND ".join(where)
    q += " ORDER BY a.appointment_datetime LIMIT %s OFFSET %s;"
    params.extend([limit, offset])
    return _fetchall(q, tuple(params))


def update_appointment(appointment_id: int, updates: Dict[str, Any]) -> Optional[Dict[str, Any]]:
    allowed = ["appointment_datetime", "duration_minutes", "reason", "status", "staff_id", "department_id"]
    sets = []
    params = []
    for k in allowed:
        if k in updates:
            sets.append(f"{k} = %s")
            params.append(updates[k])
    if not sets:
        return get_appointment(appointment_id)
    params.append(appointment_id)
    q = f"UPDATE hms.appointments SET {', '.join(sets)} WHERE appointment_id = %s RETURNING *;"
    return _fetchone(q, tuple(params))


def delete_appointment(appointment_id: int) -> int:
    return _execute("DELETE FROM hms.appointments WHERE appointment_id = %s;", (appointment_id,))

# Medical Records CRUD
def create_medical_record(payload: Dict[str, Any]) -> Dict[str, Any]:
    """
    Create a medical record for an appointment. Enforces one-record-per-appointment via DB unique constraint.
    Expected: appointment_id, patient_id, diagnosis, symptoms, observations
    """
    q = """
    INSERT INTO hms.medical_records (appointment_id, patient_id, diagnosis, symptoms, observations)
    VALUES (%s,%s,%s,%s,%s)
    RETURNING *;
    """
    params = (
        payload["appointment_id"],
        payload["patient_id"],
        payload.get("diagnosis"),
        payload.get("symptoms"),
        payload.get("observations"),
    )
    return _fetchone(q, params)


def get_medical_record(record_id: int) -> Optional[Dict[str, Any]]:
    return _fetchone("SELECT * FROM hms.medical_records WHERE record_id = %s;", (record_id,))


def get_patient_history(patient_id: int, limit: int = 50) -> List[Dict[str, Any]]:
    q = "SELECT * FROM hms.medical_records WHERE patient_id = %s ORDER BY created_at DESC LIMIT %s;"
    return _fetchall(q, (patient_id, limit))


def update_medical_record(record_id: int, updates: Dict[str, Any]) -> Optional[Dict[str, Any]]:
    allowed = ["diagnosis", "symptoms", "observations"]
    sets = []
    params = []
    for k in allowed:
        if k in updates:
            sets.append(f"{k} = %s")
            params.append(updates[k])
    if not sets:
        return get_medical_record(record_id)
    params.append(record_id)
    q = f"UPDATE hms.medical_records SET {', '.join(sets)}, updated_at = now() WHERE record_id = %s RETURNING *;"
    return _fetchone(q, tuple(params))


def delete_medical_record(record_id: int) -> int:
    return _execute("DELETE FROM hms.medical_records WHERE record_id = %s;", (record_id,))


# Prescriptions
def create_prescription(payload: Dict[str, Any]) -> Dict[str, Any]:
    """
    Creates prescription + items in a transaction.
    Payload keys:
      appointment_id, prescribed_by, notes, items: [{medication_id,dosage,frequency,duration_days}, ...]
    Medication stock decrement is handled by trigger; to ensure atomicity we use single transaction context.
    """
    with get_conn() as conn:
        try:
            with conn.cursor(cursor_factory=RealDictCursor) as cur:
                cur.execute(
                    """
                    INSERT INTO hms.prescriptions (appointment_id, prescribed_by, issued_at, notes)
                    VALUES (%s,%s,now(),%s)
                    RETURNING *;
                    """,
                    (payload["appointment_id"], payload["prescribed_by"], payload.get("notes")),
                )
                pres = cur.fetchone()
                items = payload.get("items", [])
                for it in items:
                    cur.execute(
                        """
                        INSERT INTO hms.prescription_items
                           (prescription_id, medication_id, dosage, frequency, duration_days)
                        VALUES (%s,%s,%s,%s,%s)
                        RETURNING *;
                        """,
                        (
                            pres["prescription_id"],
                            it["medication_id"],
                            it.get("dosage"),
                            it.get("frequency"),
                            it.get("duration_days"),
                        ),
                    )
                return pres
        except Exception as e:
            logger.exception("Error creating prescription: %s", e)
            raise


def get_prescription(prescription_id: int) -> Optional[Dict[str, Any]]:
    q = "SELECT * FROM hms.prescriptions WHERE prescription_id = %s;"
    pres = _fetchone(q, (prescription_id,))
    if not pres:
        return None
    items = _fetchall("SELECT * FROM hms.prescription_items WHERE prescription_id = %s;", (prescription_id,))
    pres["items"] = items
    return pres


def list_prescriptions(limit: int = 50, offset: int = 0) -> List[Dict[str, Any]]:
    return _fetchall("SELECT * FROM hms.prescriptions ORDER BY issued_at DESC LIMIT %s OFFSET %s;", (limit, offset))


# Invoices & billing helpers
def create_invoice(payload: Dict[str, Any]) -> Dict[str, Any]:
    """
    Create invoice and items atomically. payload: patient_id, appointment_id (opt), items: [{description,amount}, ...]
    """
    items = payload.get("items", [])
    total = sum(float(it["amount"]) for it in items)
    with get_conn() as conn:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute(
                """
                INSERT INTO hms.invoices (patient_id, appointment_id, issue_date, due_date, total_amount, paid_amount)
                VALUES (%s,%s,current_date,current_date + INTERVAL '30 days',%s,0)
                RETURNING *;
                """,
                (payload["patient_id"], payload.get("appointment_id"), total),
            )
            inv = cur.fetchone()
            for it in items:
                cur.execute(
                    "INSERT INTO hms.invoice_items (invoice_id, description, amount) VALUES (%s,%s,%s);",
                    (inv["invoice_id"], it["description"], it["amount"]),
                )
            return inv


def pay_invoice(invoice_id: int, amount: float) -> Optional[Dict[str, Any]]:
    """
    Add payment to invoice (increase paid_amount). Returns updated invoice.
    """
    with get_conn() as conn:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute("SELECT * FROM hms.invoices WHERE invoice_id = %s FOR UPDATE;", (invoice_id,))
            inv = cur.fetchone()
            if not inv:
                return None
            new_paid = float(inv["paid_amount"]) + float(amount)
            cur.execute("UPDATE hms.invoices SET paid_amount = %s WHERE invoice_id = %s RETURNING *;", (new_paid, invoice_id))
            updated = cur.fetchone()
            return updated


# Complex queries / reports
def appointments_with_details(limit: int = 100) -> List[Dict[str, Any]]:
    q = """
    SELECT a.appointment_id, a.appointment_datetime, a.duration_minutes, a.reason, a.status,
           p.patient_id, p.first_name AS patient_first_name, p.last_name AS patient_last_name,
           s.staff_id, s.first_name AS staff_first_name, s.last_name AS staff_last_name,
           d.department_id, d.name AS department_name
    FROM hms.appointments a
    JOIN hms.patients p ON p.patient_id = a.patient_id
    LEFT JOIN hms.medical_staff s ON s.staff_id = a.staff_id
    LEFT JOIN hms.departments d ON d.department_id = a.department_id
    ORDER BY a.appointment_datetime
    LIMIT %s;
    """
    return _fetchall(q, (limit,))


def revenue_per_department() -> List[Dict[str, Any]]:
    """
    Sum of invoice items grouped by department (via appointments).
    Departments without revenue will have 0.
    """
    q = """
    SELECT d.department_id, d.name,
           COALESCE(SUM(ii.amount),0) AS revenue
    FROM hms.departments d
    LEFT JOIN hms.appointments a ON a.department_id = d.department_id
    LEFT JOIN hms.invoices inv ON inv.appointment_id = a.appointment_id
    LEFT JOIN hms.invoice_items ii ON ii.invoice_id = inv.invoice_id
    GROUP BY d.department_id, d.name
    ORDER BY revenue DESC;
    """
    return _fetchall(q)


def doctors_with_more_than_x_appointments(month: int, year: int, threshold: int = 10) -> List[Dict[str, Any]]:
    """
    Return doctors (medical_staff.role = 'Doctor') who have more than `threshold` appointments in given month/year.
    """
    q = """
    SELECT s.staff_id, s.first_name, s.last_name, s.specialization, COUNT(a.appointment_id) AS appointments_count
    FROM hms.medical_staff s
    JOIN hms.appointments a ON a.staff_id = s.staff_id
    WHERE s.role = 'Doctor'
      AND EXTRACT(MONTH FROM a.appointment_datetime) = %s
      AND EXTRACT(YEAR FROM a.appointment_datetime) = %s
    GROUP BY s.staff_id, s.first_name, s.last_name, s.specialization
    HAVING COUNT(a.appointment_id) > %s
    ORDER BY appointments_count DESC;
    """
    return _fetchall(q, (month, year, threshold))


def patients_with_outstanding_invoices() -> List[Dict[str, Any]]:
    """
    Patients with invoices where paid_amount < total_amount
    """
    q = """
    SELECT p.patient_id, p.first_name, p.last_name, inv.invoice_id, inv.total_amount, inv.paid_amount, (inv.total_amount - inv.paid_amount) AS balance
    FROM hms.patients p
    JOIN hms.invoices inv ON inv.patient_id = p.patient_id
    WHERE inv.paid_amount < inv.total_amount
    ORDER BY balance DESC;
    """
    return _fetchall(q)


def available_appointments_by_date(target_date: str) -> List[Dict[str, Any]]:
    """
    List staff and their free slots on a given date (coarse: shows staff with no appointments that day)
    target_date format 'YYYY-MM-DD'
    """
    q = """
    SELECT s.staff_id, s.first_name, s.last_name, s.specialization
    FROM hms.medical_staff s
    WHERE s.is_active = TRUE
      AND NOT EXISTS (
          SELECT 1 FROM hms.appointments a
          WHERE a.staff_id = s.staff_id
            AND a.appointment_datetime::date = %s
      );
    """
    return _fetchall(q, (target_date,))


def search_patients_by_name(name_query: str, limit: int = 20) -> List[Dict[str, Any]]:
    q = """
    SELECT * FROM hms.patients
    WHERE lower(first_name || ' ' || last_name) LIKE %s
    ORDER BY patient_id
    LIMIT %s;
    """
    return _fetchall(q, (f"%{name_query.lower()}%", limit))

